package com.asiainfo.dbConnect;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Scanner;

/**
 * @Author Administrator
 * @Date 2022/1/27 0027 14:10
 * @Version 1.0
 */
public class DBConnectTestApplication {
    private final static Logger log = LoggerFactory.getLogger(DBConnectTestApplication.class);
    public static void main(String[] args) {
        DBConnectTestApplication dbConnectTestApplication = new DBConnectTestApplication();
        System.out.println("请输入需要连接的数据库:");
        Scanner input_dbName = new Scanner(System.in);
        String dbName = input_dbName.nextLine().trim();
        // 初始化配置文件参数
        ConfigParamsUtil.init(dbName);
        log.info("driver:" + ConfigParams.driver);
        log.info("url:" + ConfigParams.url);
        log.info("username:" + ConfigParams.username);
        log.info("password:" + ConfigParams.password);
        Connection connection = null;
        try {
            connection = DBHelper.getConnection();
            while (true) {
                System.out.println("请输入需要执行的SQL【quit退出】:");
                Scanner input = new Scanner(System.in);
                String sql = input.nextLine().trim();
                if(sql.equalsIgnoreCase("quit")){
                    break;
                }
                String sql_prefix = sql.substring(0,6);
                boolean isQuerySql = sql_prefix.equalsIgnoreCase("select");
                try {
                    if(isQuerySql){
                        // 执行查询语句
                        dbConnectTestApplication.queryDB(sql,connection);
                    }else{
                        // 执行其他语句
                        dbConnectTestApplication.updateDB(sql,connection);
                    }
                } catch (Exception e) {
                    System.out.println();
                    System.out.println("############################################");
                    System.out.println("SQL执行出错:");
                    e.printStackTrace();
                    System.out.println("############################################");
                }
            }
            DBHelper.close(connection);
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }

    }

    /**
     * 查询SQL
     * @param sql
     * @param connection
     * @throws SQLException
     */
    private void queryDB(String sql,Connection connection) throws SQLException {
        System.out.println("执行的SQL:");
        System.out.println(sql);
        PreparedStatement preparedStatement_query = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement_query.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int col_count=metaData.getColumnCount();
        System.out.print("RowId");
        System.out.print("|");
        for (int i = 1;i <= col_count;i++){
            String columnName = metaData.getColumnName(i);
            System.out.print(columnName);
            System.out.print("|");
        }
        System.out.println("\n------------------------------------------------------------------------------------------------------");
        int row_count = 1;
        while (resultSet.next()){
            System.out.print(row_count);
            System.out.print("\t");
            for (int i = 1;i <= col_count;i++){
                String colType = metaData.getColumnClassName(i);
                String col = getColStr(colType,resultSet,i);
                System.out.print(col);
                System.out.print("\t");

            }
            row_count++;
            System.out.println("\n------------------------------------------------------------------------------------------------------");
        }
    }

    /**
     * 其他语句，如：insert,delete,update等执行语句
     * @param sql
     * @param connection
     * @throws SQLException
     */
    private void updateDB(String sql,Connection connection) throws SQLException {
        System.out.println("********************************************");
        System.out.println("执行的SQL:");
        System.out.println(sql);
        PreparedStatement preparedStatement_insert = connection.prepareStatement(sql);
        int result = preparedStatement_insert.executeUpdate();
        System.out.println("############################################");
        System.out.println("[" + result + " successful]");
        System.out.println("############################################");
    }

    private String getColStr(String colType,ResultSet resultSet,int colCount) throws SQLException {
        String result = "";
        if("java.lang.Integer".equals(colType)){
            result = resultSet.getInt(colCount) + "";
        }else if("java.lang.String".equals(colType)){
            result = resultSet.getString(colCount);
        }else if("java.sql.Timestamp".equals(colType)){
            Timestamp timestamp = resultSet.getTimestamp(colCount);
            if(timestamp == null){
                result = "";
            }else{
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                result = df.format(timestamp);
            }
        }else if("java.sql.Date".equals(colType)){
            Date date = resultSet.getDate(colCount);
            if(date == null){
                result = "";
            }else{
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                result = df.format(date);
            }
        }else if("java.lang.Double".equals(colType)){
            double val = resultSet.getDouble(colCount);
            result = val + "";
        }else if("java.lang.Float".equals(colType)){
            float val = resultSet.getFloat(colCount);
            result = val + "";
        }else if("java.sql.Time".equals(colType)){
            Time val = resultSet.getTime(colCount);
            if(val == null){
                result = "";
            }else{
                SimpleDateFormat df = new SimpleDateFormat("HH:mm:ss");
                result = df.format(val);
            }
        }else if("java.math.BigDecimal".equals(colType)){
            BigDecimal val = resultSet.getBigDecimal(colCount);
            result = val.toString();
        }else if("java.lang.Boolean".equals(colType)){
            boolean val = resultSet.getBoolean(colCount);
            result = val + "";
        }else if("java.sql.Blob".equals(colType)){
            Blob val = resultSet.getBlob(colCount);
            byte[] bytes = val.getBytes(1, (int) val.length());
            result = new String(bytes);
        }else{
            Object val = resultSet.getObject(colCount);
            result = val == null?"":val.toString();
        }
        return result;
    }
}
